package pl.drypisy;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
/**
 * @author Michal Drobniak
 */
public class DBAdapter {
    
    //Route table constants
    static final String KEY_ROUTEID = "id";
    static final String KEY_RNAME= "name";
    static final String KEY_RABOUT = "about";
    static final String KEY_RDATE = "date";
    
    //Checkpoint table constants
    static final String KEY_CHID = "id";
    static final String KEY_CH_ROUTE_ID = "route_id";
    static final String KEY_NAME = "name";
    static final String KEY_LAT = "lat";
    static final String KEY_LON = "lon";
    static final String KEY_TASK = "task";
    static final String KEY_ANSWER = "answer";
    static final String KEY_HINT1 = "hint1";
    static final String KEY_HINT2 = "hint2";
    static final String KEY_HINT3 = "hint3";
    static final String KEY_HINT4 = "hint4";
   
    static final String TAG = "DBAdapter";

    static final String DB_NAME = "Routes";
    static final String DB_T_R = "route";
    static final String DB_T_CH = "checkpoint";
    static final int DB_VERSION = 5;

    static final String DB_CREATE_ROUTES =
        "create table " + DB_T_R + " ("+ KEY_ROUTEID +" integer primary key autoincrement, "
        + KEY_RNAME + " text not null, "
        + KEY_RABOUT + " text not null, "
        + KEY_RDATE + " text not null);";
    
    static final String DB_CREATE_CHECKPOINTS =
            "create table " + DB_T_CH + " ("+ KEY_CHID +" integer primary key autoincrement, "
            + KEY_CH_ROUTE_ID + " integer not null, "
            + KEY_NAME + " text not null, "
            + KEY_LAT + " text not null, "
            + KEY_LON + " text not null, "
            + KEY_TASK + " text not null, "
            + KEY_ANSWER + " text not null, "
            + KEY_HINT1 + " text not null, "
            + KEY_HINT2 + " text not null, "
            + KEY_HINT3 + " text not null, "
            + KEY_HINT4 + " text not null) ";
    /**
     * 0 CHID
     * 1 RID
     * 2 NAME
     * 3 LAT
     * 4 LON
     * 5 TASK
     * 6 ANSWER
     * 7 HINT1
     * 8 HINT2
     * 9 HINT3
     * 10 HINT4
     */
            
    final Context context;

    DatabaseHelper DBHelper;
    SQLiteDatabase db;
    
    public DBAdapter(Context ctx)
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    private static class DatabaseHelper extends SQLiteOpenHelper
    {
        DatabaseHelper(Context context)
        {
            super(context, DB_NAME, null, DB_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db)
        {
            try {
                db.execSQL(DB_CREATE_ROUTES);
                db.execSQL(DB_CREATE_CHECKPOINTS);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            db.execSQL("DROP TABLE IF EXISTS " + DB_T_R);
            db.execSQL("DROP TABLE IF EXISTS " + DB_T_CH);
            onCreate(db);
        }
    }

    //---opens the database---
    public DBAdapter open() throws SQLException 
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    //---closes the database---
    public void close() 
    {
        DBHelper.close();
    }

    //insert a route into the database
    public long insertRoute(String name, String about, String date) 
    {
    	
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_RNAME, name);
        initialValues.put(KEY_RABOUT, about);
        initialValues.put(KEY_RDATE, date);
        return db.insert(DB_T_R, null, initialValues);
    }
    
    //insert a checkpoint into the database
    public long insertCheckpoint(long routeId, String name, String lat, String lon, String task, String answer, String hint1, String hint2, String hint3, String hint4) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_CH_ROUTE_ID, routeId);
        initialValues.put(KEY_NAME, name);
        initialValues.put(KEY_LAT, lat);
        initialValues.put(KEY_LON, lon);
        initialValues.put(KEY_TASK, task);
        initialValues.put(KEY_ANSWER, answer);
        initialValues.put(KEY_HINT1, hint1);
        initialValues.put(KEY_HINT2, hint2);
        initialValues.put(KEY_HINT3, hint3);
        initialValues.put(KEY_HINT4, hint4);
        
        return db.insert(DB_T_CH, null, initialValues);
    }

    //deletes a particular route
    public boolean deleteRoute(int routeId) 
    {	
        int routeDel=db.delete(DB_T_R, KEY_ROUTEID + "=" + routeId, null);
        int chDel=db.delete(DB_T_CH, KEY_CH_ROUTE_ID +"="+ routeId, null);
        
        if(routeDel>0 && chDel>0) return true;
        else return false;
    }

    //retrieves all the routes
    public Cursor getAllRoutes()
    {
    	Log.d("db","getAllRoutes");
        return db.query(DB_T_R, new String[] {KEY_ROUTEID, 
        		KEY_RNAME,KEY_RABOUT,KEY_RDATE}, null, null, null, null, null);
    }
    
    //retrieves all the checkpoints
    public Cursor getAllCheckpoints()
    {
        return db.query(DB_T_CH, new String[] {KEY_CHID, KEY_CH_ROUTE_ID,
                KEY_NAME, KEY_LAT, KEY_LON, KEY_TASK, KEY_ANSWER, KEY_HINT1,KEY_HINT2,KEY_HINT3,KEY_HINT4}, null, null, null, null, null);
    }

    //retrieves a particular route
    public Cursor getRoute(long rowId) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, DB_T_R, new String[] {KEY_ROUTEID, 
                		KEY_RNAME,KEY_RABOUT,KEY_RDATE}, KEY_ROUTEID + "=" + rowId, 
                		null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    
    //retrieves a particular checkpoint
    public Cursor getCheckpoints(long routeId) throws SQLException 
    {
        Cursor mCursor =
                db.query(true, DB_T_CH, new String[] {KEY_CHID, KEY_CH_ROUTE_ID,
                		KEY_NAME, KEY_LAT, KEY_LON, KEY_TASK, KEY_ANSWER, KEY_HINT1,KEY_HINT2,KEY_HINT3,KEY_HINT4},
                		KEY_CH_ROUTE_ID +"="+ routeId ,
                		null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
    
    public Cursor getCheckpoint(long chId) throws SQLException {
    	Cursor mCursor =
    			db.query(true, DB_T_CH, new String[] {KEY_CHID, KEY_CH_ROUTE_ID,
                		KEY_NAME, KEY_LAT, KEY_LON, KEY_TASK, KEY_ANSWER, KEY_HINT1,KEY_HINT2,KEY_HINT3,KEY_HINT4}, 
                		KEY_CHID + "=" + chId, 
                		null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }
}